package ltd.hanamaki.review.jdbc.jdbctest;

import ltd.hanamaki.review.jdbc.util.JDBCUtils;
import ltd.hanamaki.review.jdbc.util.JDBCUtils_1;

import java.sql.*;

public class JDBCUtilsTest {

    static Connection connection;
    static Statement statement;
    static PreparedStatement preparedStatement;
    static ResultSet resultSet;


    public static void main(String[] args) {
//        query();
//        setStatement("' or '1=1", "' or '1=1");
//        setStatement("hanamaki", "123456");

//        System.out.println("===========");
//        setPreparedStatement("hanamaki", "123456");
//        setPreparedStatement("' or '1=1", "' or '1=1");
//
//        insert("test","123456");

//        delete("test");

//        update("hanamaki", "56789");
        transaction(1, 2, 500);
    }

    /* 数据库查询 */
    public static void query() {
        connection = JDBCUtils_1.getCon();
        /* 创建sql 的查询 对象  statement */
        try {
            statement = connection.createStatement();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        /* 编写 sql语句 */
        String sql = "select * from students";

        try {
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getObject(2));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.release(connection, statement, resultSet, null);
        }

    }

    /* sql 注入  */
    /* 模拟登录业务 */
    public static void setStatement(String username, String password) {

        connection = JDBCUtils_1.getCon();
        /* 创建sql 的查询 对象  statement */
        try {
            statement = connection.createStatement();
            /* 编写 sql语句 */
            String sql = "select * from login where username = '" + username + "' and password = '" + password + "'";

            System.out.println(sql);
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getObject(1) + "----" + resultSet.getObject(2));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


    }

    /* 避免 sql  注入 */
    /* 模拟登录业务 */
    public static void setPreparedStatement(String username, String password) {

        System.out.println(username + "" + password);

        connection = JDBCUtils_1.getCon();
        /* 创建sql 的查询 对象  statement */
        try {
            /* 编写 sql语句 */
            String sql = "select * from login where username = ? and password = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);

            resultSet = preparedStatement.executeQuery();
            System.out.println(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getObject(1) + "----" + resultSet.getObject(2));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }


    /* CRUD 增删改   事务 */
    public static void insert(String username, String password) {


        connection = JDBCUtils_1.getCon();
        /* 创建sql 的查询 对象  statement */
        try {
            /* 编写 sql语句 */
            String sql = "insert into login values(?,?);";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);

            /* 执行插入 */
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功");
            }

        } catch (SQLException throwables) {
        } finally {
            JDBCUtils_1.release(connection, null, null, preparedStatement);
        }

    }

    /* CRUD 增删改   事务 */
    public static void delete(String username) {


        connection = JDBCUtils_1.getCon();
        /* 创建sql 的查询 对象  statement */
        try {
            /* 编写 sql语句 */
            String sql = "delete from login where username = ?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);

            /* 执行插入 */
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
        } finally {
            JDBCUtils_1.release(connection, null, null, preparedStatement);
        }

    }


    /* CRUD 增删改   事务 */
    public static void update(String username, String password) {


        connection = JDBCUtils_1.getCon();
        /* 创建sql 的查询 对象  statement */
        try {
            /* 编写 sql语句 */
            String sql = "update login set password = ? where username = ?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, password);
            preparedStatement.setString(2, username);

            /* 执行插入 */
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("修改成功");
            }

        } catch (SQLException throwables) {
        } finally {
            JDBCUtils_1.release(connection, null, null, preparedStatement);
        }

    }


    /* 编写 事务 */

    /**
     * 编写 转账的事务 测试
     *
     * @param transferPersonId
     * @param payeeId
     * @param money
     */
    public static void transaction(int transferPersonId, int payeeId, int money){
        /* 连接 数据库 */
        connection = JDBCUtils_1.getCon();
        /* 如果想要 编写事务就必须开启事务 或者将 事务的 自动提交 关闭 */
        try {
            connection.setAutoCommit(false);
            /* 想要编写 转账的操作 需要 两句sql语句 */
            String sql1 = "update mone set money = money - ? where id = ?";
            String sql2 = "update money set money = money + ? where id = ?";

            PreparedStatement p1 = connection.prepareStatement(sql1);
            p1.setInt(1,money);
            p1.setInt(2,transferPersonId);
            p1.executeUpdate();

            PreparedStatement p2 = connection.prepareStatement(sql2);
            p2.setInt(1, money);
            p2.setInt(2, payeeId);
            p2.executeUpdate();

            /* 如果 都成功就 提交 */
            connection.commit();
            System.out.println("转账成功");

        } catch (SQLException throwables) {
            try {
                connection.rollback();
                System.out.println("转账失败");
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }





    }


}
